﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CapaDeEntidades;
using System.Data.SqlClient;
using System.Data;

namespace CapaDatos
{
   public  class SocioDatos
    {
       public static string connectionstring = "Initial Catalog=Biblioteca;Data Source=localhost\\SQLEXPRESS;Integrated Security=SSPI;";
      

        public static bool RegistrarSocio(Socio socio)
        {
            bool respu = false;
            SqlConnection conn = new SqlConnection(connectionstring);

            try
            {
               long dni = 0;
               string nombre = "";
               string mail = "";
               string usuario = "";
               string pass = "";

             if (socio.Dni != null)
            {
                dni = socio.Dni;
            }
            if (socio.NombreApellido != null)
            {
                nombre = socio.NombreApellido;
            }
            if (socio.email != null)
            {
                mail = socio.email;
            }
            if (socio.Usuario != null)
            {
                usuario = socio.Usuario;
            }
            if (socio.Pass != null)
            {
                pass = socio.Pass;
            }
                conn.Open();
                SqlCommand cmd = new SqlCommand("sp_CrearSocio", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@dni", dni);
                cmd.Parameters.AddWithValue("@nombre ", nombre);
                cmd.Parameters.AddWithValue("@email", mail);
                cmd.Parameters.AddWithValue("@nomUsuario", usuario);
                cmd.Parameters.AddWithValue("@password", pass);

                cmd.Parameters.AddWithValue("@alta_usuario", respu);


                cmd.Parameters["@alta_usuario"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                respu = Convert.ToBoolean(cmd.Parameters["@alta_usuario"].Value);

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return respu;
        }

        public static bool DarBajaSocio(Socio socio)
        {
            bool respu = false;
            SqlConnection conn = new SqlConnection(connectionstring);

            try
            {
                long dni = 0;
               
                if (socio.Dni != null)
                {
                    dni = socio.Dni;
                }

                SqlCommand cmd = new SqlCommand("sp_EliminarSocio", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@dni", dni);
                cmd.Parameters.AddWithValue("@baja_socio", respu);
                cmd.Parameters["@baja_socio"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                respu = Convert.ToBoolean(cmd.Parameters["@baja_socio"].Value);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return respu;


        }

        public static bool ModificarSocio(Socio socio)
        {
            bool respu = false;
            SqlConnection conn = new SqlConnection(connectionstring);

            try
            {
                long dni = 0;
                string nombre = "";
                string mail = "";
                string usuario = "";
                string pass = "";

                if (socio.Dni != null)
                {
                    dni = socio.Dni;
                }
                if (socio.NombreApellido != null)
                {
                    nombre = socio.NombreApellido;
                }
                if (socio.email != null)
                {
                    mail = socio.email;
                }
                if (socio.Usuario != null)
                {
                    usuario = socio.Usuario;
                }
                if (socio.Pass != null)
                {
                    pass = socio.Pass;
                }

                SqlCommand cmd = new SqlCommand("sp_ModificarSocio", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@dni", dni);
                cmd.Parameters.AddWithValue("@nombre ", nombre);
                cmd.Parameters.AddWithValue("@email", mail);
                cmd.Parameters.AddWithValue("@nomUsuario", usuario);
                cmd.Parameters.AddWithValue("@password", pass);

                cmd.Parameters.AddWithValue("@update_socio", respu);


                cmd.Parameters["@update_socio"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                respu = Convert.ToBoolean(cmd.Parameters["@update_socio"].Value);

            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return respu;
        }

        public static DataSet ConsultaSocio(Socio socio)
        {

            SqlConnection conn = new SqlConnection(connectionstring);
            DataSet ds = new DataSet();
            try
            {
                long dni = 0;

                if (socio.Dni != null)
                {
                    dni = socio.Dni;
                }

                SqlCommand cmd = new SqlCommand("sp_NOMBRE DEL STORE PROCEDURE", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@dni ", dni);

                //LLENO EL DATASET
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }

        public static DataSet VerificaUsuario(string nombre, string password)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(connectionstring);

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("sp_ValidarUsuario", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Usuario", nombre);
                cmd.Parameters.AddWithValue("@Password", password);
                //LLENO EL DATASET
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }

        public static DataSet GetSocioById(int idSocio)
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            DataSet ds = new DataSet();

            try
            {
                SqlCommand cmd = new SqlCommand("sp_BuscarSocioById", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@IdSocio", idSocio);
                

                //LLENO EL DATASET
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                System.Diagnostics.Debug.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }


      

    }
}
